﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;


namespace QuanLySuKien.model
{

    public class SuKienModel
    {
        public SuKienModel()
        {

        }

        //danh sach loai sk
        public DataTable DanhSachLoaiSK()
        {
            DataAccess da = new DataAccess();
            string sql = "SELECT * FROM LoaiSK ORDER BY tt";
            return da.Select(sql);
        }


        //create su kien
        public int TaoSuKien(string maNV, string maKH, string tenSK, string ngayBatDau, string ngayKetThuc, int maDD, string maLoaiSK, string moTa)
        {
            DataAccess da = new DataAccess();
            try
            {
                string sql = "INSERT INTO FROM sukien(MaNV,MaKH,TenSK,NgayBatDau,NgayKetThuc,MaDD,MaLoaiSK,Mota)"
        + " VALUES('" + maNV + "','" + maKH + "','" + tenSK + "','" + ngayBatDau + "','" + ngayKetThuc + "'," + maDD + ",'" + maLoaiSK + "','" + moTa + "')";

                return da.InsertUpdate(sql);
            }
            catch
            {
                return 0;
            }

        }

        //thiet bi cua su kien
        public int SuKien_ThietBi(int maSK, string maTB, int soluong, decimal gia)
        {
            DataAccess da = new DataAccess();
            string sql = "INSERT INTO SuKien_ThietBi VALUES(" + maSK + ",'" + maTB + "'," + soluong + "," + gia + ")";
            return da.InsertUpdate(sql);

        }


        //nhan su cua su kien
        public int SuKien_NhanSu(int maSK, string maNS, int soluong, decimal gia)
        {
            DataAccess da = new DataAccess();
            string sql = "INSERT INTO SuKien_ThietBi VALUES(" + maSK + ",'" + maNS + "'," + soluong + "," + gia + ")";
            return da.InsertUpdate(sql);
        }


        //lay su kien cuoi cung cua nhan vien dang lam viec
        public int LaySuKienCuoiCung(string maNV)
        {
            DataAccess da = new DataAccess();
            string sql = "SELECT TOP(1) maKS FROM SuKien WHERE maNV = " + maNV;
            return Convert.ToInt32(da.Select(sql).Rows[0]["maSK"].ToString());
        }





        //---------------------------------------------
        //danh sach su kien
        public DataTable DanhSachSuKien()
        {
            DataAccess da = new DataAccess();
            string sql = "SELECT * FROM SuKien";

            return null;
        }


        //-----------------------------------------------
        //danh sach dia diem kha dung
        public DataTable DanhSachDiaDiemKhaDung()
        {
            DataAccess da = new DataAccess();
            try
            {
                string sql = "SELECT DiaDiem.MaDD, TenDiaDiem,TenKhuVuc,Gia, SucChua FROM DiaDiem,KhuVuc WHERE DiaDiem.MaKV = KhuVuc.MaKV AND TrangThai = 0 AND MaDD not in " +
                            "(" +
                            "SELECT MaDD FROM sukien WHERE  GETDATE() >= NgayBatDau AND GETDATE() <= NgayKetThuc"
                            + ")";
                return da.Select(sql);
            }
            catch
            {
                return null;
            }

        }


        //-----------------------------------------------
        //danh sach thiet bi kha dung
        public DataTable DanhSachThietBiKhaDung()
        {
            DataAccess da = new DataAccess();
            try
            {
                string sql = "SELECT * FROM ThietBi WHERE TrangThai = 0";
                return da.Select(sql);
            }
            catch
            {
                return null;
            }

        }

        //-----------------------------------------------
        //danh sach nhan su kha dung
        public DataTable DanhSachNhanSuKhaDung()
        {
            DataAccess da = new DataAccess();
            try
            {
                string sql = "SELECT * FROM NhanSu WHERE TrangThai = 0";
                return da.Select(sql);
            }
            catch
            {
                return null;
            }

        }


        //------------------------------------------------------------------
        //lay dia diem qua qua MaDD
        public DataTable DiaDiemByMaDD(string maDD)
        {

            DataAccess da = new DataAccess();
            try
            {
                string sql = "SELECT * FROM DiaDiem,KhuVuc WHERE KhuVuc.MaKV = DiaDiem.MaKV AND MaDD = " + maDD;
                return da.Select(sql);
            }
            catch
            {
                return null;
            }
        }


        //------------------------------------------------------------------
        //lay cac thiet bi qua qua MaTB
        public DataTable ThietBiByMaTB(string maTB)
        {
            DataAccess da = new DataAccess();
            try
            {
                string sql = "SELECT * FROM ThietBi WHERE MaTB = '" + maTB + "'";
                return da.Select(sql);
            }
            catch
            {
                return null;
            }
        }


        //------------------------------------------------------------------
        //lay cac nhan su qua qua MaNS
        public DataTable NhanSuByMaNS(string maNS)
        {
            DataAccess da = new DataAccess();
            try
            {
                string sql = "SELECT * FROM NhanSu WHERE MaNS = '" + maNS + "'";
                return da.Select(sql);
            }
            catch
            {
                return null;
            }
        }




    }
}